with t1 as (
    SELECT
            t.datetime,
            t.shipmentno,
            t.shipment_name,
            t.start_network_name,
            t.start_network_code,
            t.end_network_name,
            t.end_network_code,
            t.billing_way,
            t.shipmentstate,
            sum(if(t.is_loading=1,nvl(t.bill_weight, 0),0)) loading_weight,
            0 as unloading_weight,
            max(t.planneddeparturetime) planneddeparturetime,
            max(t.actualdeparturetime) actualdeparturetime,
            max(t.plannedarrivaltime) plannedarrivaltime,
            max(t.actualarrivaltime) actualarrivaltime,
            SIZE(COLLECT_SET(package_code)) loading_package,
            0 unloading_package,
            0 rough_unloading_weight,
            vehicletype_id,
            vehicletype_name,
            settlement_vehicle_type_id,
            settlement_vehicle_type_name
             FROM jms_dm.dm_shipno_weight_detail_dt t
            WHERE dt between '{{ execution_date | date_add(-13) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
            GROUP BY t.datetime,
                     t.shipmentno,
                     t.shipment_name,
                     t.start_network_code,
                     t.start_network_name,
                     t.end_network_name,
                     t.end_network_code,
                     t.cnt,
                     t.billing_way,
                     t.shipmentstate,
                     vehicletype_id,
                     vehicletype_name,
                     settlement_vehicle_type_id,
                     settlement_vehicle_type_name
       union all
   SELECT
            t.datetime,
            t.shipmentno,
            t.shipment_name,
            t.start_network_name,
            t.start_network_code,
            t.end_network_name,
            t.end_network_code,
            t.billing_way,
            t.shipmentstate,
            0 as loading_weight ,
            sum(if(is_arri=1,nvl(t.bill_weight, 0),0)) unloading_weight,
            max(t.planneddeparturetime) planneddeparturetime,
            max(t.actualdeparturetime) actualdeparturetime,
            max(t.plannedarrivaltime) plannedarrivaltime,
            max(t.actualarrivaltime) actualarrivaltime,
            0 loading_package,
            SIZE(COLLECT_SET(if(is_arri=1,package_code,null))) unloading_package,
            sum(if(is_rough_judgement=1,nvl(t.bill_weight, 0),0)) rough_unloading_weight,
            vehicletype_id,
            vehicletype_name,
            settlement_vehicle_type_id,
            settlement_vehicle_type_name
             FROM jms_dm.dm_shipno_weight_detail_dt t
            WHERE dt between '{{ execution_date | date_add(-13) | cst_ds }}' and '{{ execution_date |  cst_ds }}'
            GROUP BY t.datetime,
                     t.shipmentno,
                     t.shipment_name,
                     t.start_network_code,
                     t.start_network_name,
                     t.end_network_code,
                     t.end_network_name,
                     t.billing_way,
                     t.shipmentstate,
                     vehicletype_id,
                     vehicletype_name,
                     settlement_vehicle_type_id,
                     settlement_vehicle_type_name
)
insert overwrite table jms_dm.dm_shipno_weight_sum_dt partition(dt)
   select
            datetime,
            shipmentno,
            start_network_code,
            end_network_code ,
            shipment_name,
            start_network_name,
            end_network_name,
            billing_way,
            shipmentstate,
            sum(loading_weight)   as loading_weight,
            sum(unloading_weight) as unloading_weight,
            max(planneddeparturetime) planneddeparturetime,
            max(actualdeparturetime) actualdeparturetime,
            max(plannedarrivaltime) plannedarrivaltime,
            max(actualarrivaltime) actualarrivaltime,
            from_unixtime(unix_timestamp()),
            1 as is_delete,
            sum(loading_package) as loading_package,
            sum(unloading_package) as unloading_package,
            sum(rough_unloading_weight) as rough_unloading_weight,
            vehicletype_id,
            vehicletype_name,
            settlement_vehicle_type_id,
            settlement_vehicle_type_name,
            datetime as dt
    from t1
      group by
         datetime,
         shipmentno,
         shipment_name,
         start_network_code,
         start_network_name,
         end_network_code,
         end_network_name,
         billing_way,
         shipmentstate,
         vehicletype_id,
         vehicletype_name,
         settlement_vehicle_type_id,
         settlement_vehicle_type_name
distribute by 1;
